import pool from '../database';
import type { BusData } from '../../types/DataTypes';

export class BusDAO {
    // 新增巴士
    static async addBus(bus: BusData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `INSERT INTO Bus (location, price, totalBus, availBus) 
                 VALUES (?, ?, ?, ?)`,
                [bus.location, bus.price, bus.totalBus, bus.availBus]
            );
            return (result as any).affectedRows > 0;
        } catch (error: any) {
            if (error.code === 'ER_DUP_ENTRY') {
                throw new Error(`巴士位置 ${bus.location} 已存在`);
            }
            throw error;
        } finally {
            connection.release();
        }
    }

    // 批量新增巴士
    static async addBuses(buses: BusData[]): Promise<number> {
        const connection = await pool.getConnection();
        try {
            await connection.beginTransaction();

            let successCount = 0;
            for (const bus of buses) {
                try {
                    const [result] = await connection.execute(
                        `INSERT INTO Bus (location, price, totalBus, availBus)
                         VALUES (?, ?, ?, ?)`,
                        [bus.location, bus.price, bus.totalBus, bus.availBus]
                    );
                    if ((result as any).affectedRows > 0) {
                        successCount++;
                    }
                } catch (error: any) {
                    if (error.code !== 'ER_DUP_ENTRY') {
                        throw error;
                    }
                    // 重复记录跳过，继续插入其他记录
                }
            }

            await connection.commit();
            return successCount;
        } catch (error) {
            await connection.rollback();
            throw error;
        } finally {
            connection.release();
        }
    }

    // 获取所有巴士
    static async getAllBuses(): Promise<BusData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Bus ORDER BY location'
            );
            return rows as BusData[];
        } finally {
            connection.release();
        }
    }

    // 根据位置模糊查询巴士
    static async searchBusesByLocation(keyword: string): Promise<BusData[]> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Bus WHERE location LIKE ? ORDER BY location',
                [`%${keyword}%`]
            );
            return rows as BusData[];
        } finally {
            connection.release();
        }
    }

    // 更新巴士数据
    static async updateBus(busData: BusData): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            // 构建动态更新语句
            const fields = [];
            const values = [];

            if (busData.price !== undefined) {
                fields.push('price = ?');
                values.push(busData.price);
            }
            if (busData.totalBus !== undefined) {
                fields.push('totalBus = ?');
                values.push(busData.totalBus);
            }
            if (busData.availBus !== undefined) {
                fields.push('availBus = ?');
                values.push(busData.availBus);
            }

            if (fields.length === 0) {
                return false;
            }

            values.push(busData.location);

            const [result] = await connection.execute(
                `UPDATE Bus SET ${fields.join(', ')} WHERE location = ?`,
                values
            );

            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 可用数量增加
    static async incrementBusAvailability(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Bus 
                     SET availBus = availBus + 1 
                     WHERE location = ? 
                     AND availBus < totalBus`,
                        [location]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 可用数量减少
    static async decrementBusAvailability(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                `UPDATE Bus 
                     SET availBus = availBus - 1 
                     WHERE location = ? 
                     AND availBus > 0`,
                        [location]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 删除巴士
    static async deleteBus(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [result] = await connection.execute(
                'DELETE FROM Bus WHERE location = ?',
                [location]
            );
            return (result as any).affectedRows > 0;
        } finally {
            connection.release();
        }
    }

    // 根据位置精确查询巴士
    static async getBusByLocation(location: string): Promise<BusData | null> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT * FROM Bus WHERE location = ?',
                [location]
            );
            const buses = rows as BusData[];
            return buses.length > 0 ? buses[0] : null;
        } finally {
            connection.release();
        }
    }

    // 检查巴士是否存在
    static async busExists(location: string): Promise<boolean> {
        const connection = await pool.getConnection();
        try {
            const [rows] = await connection.execute(
                'SELECT 1 FROM Bus WHERE location = ?',
                [location]
            );
            return (rows as any[]).length > 0;
        } finally {
            connection.release();
        }
    }
}